import sqlite3
from sqlite3 import Error

def create_connection(db_file):
    """ 创建数据库连接 """
    conn = None
    try:
        conn = sqlite3.connect(db_file)
        print(f"成功连接到SQLite数据库，版本：{sqlite3.version}")
        return conn
    except Error as e:
        print(e)
    return conn

def create_tables(conn):
    """ 创建数据表结构 """
    sql_statements = [
        # 启用外键约束
        "PRAGMA foreign_keys = ON;",
        
        # 基础表
        """CREATE TABLE IF NOT EXISTS Cities (
            CityID INTEGER PRIMARY KEY,
            CityName TEXT,
            State TEXT,
            Tier INTEGER
        );""",
        
        """CREATE TABLE IF NOT EXISTS Boards (
            BoardID INTEGER PRIMARY KEY,
            BoardName TEXT UNIQUE
        );""",
        
        """CREATE TABLE IF NOT EXISTS Students (
            StudentID INTEGER PRIMARY KEY,
            Gender TEXT CHECK(Gender IN ('M', 'F')),
            DOB DATE
        );""",
        
        # 教育相关表
        """CREATE TABLE IF NOT EXISTS Education (
            EducationID INTEGER PRIMARY KEY,
            StudentID INTEGER,
            EducationLevel TEXT CHECK(EducationLevel IN ('10th', '12th')),
            Percentage REAL,
            BoardID INTEGER,
            GraduationYear INTEGER,
            FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
            FOREIGN KEY (BoardID) REFERENCES Boards(BoardID)
        );""",
        
        """CREATE TABLE IF NOT EXISTS Colleges (
            CollegeID INTEGER PRIMARY KEY,
            CollegeName TEXT,
            Tier INTEGER,
            CityID INTEGER,
            State TEXT,
            FOREIGN KEY (CityID) REFERENCES Cities(CityID)
        );""",
        
        """CREATE TABLE IF NOT EXISTS DegreeSpecializations (
            DegreeSpecID INTEGER PRIMARY KEY,
            Degree TEXT,
            Specialization TEXT,
            UNIQUE(Degree, Specialization)
        );""",
        
        """CREATE TABLE IF NOT EXISTS CollegePerformance (
            StudentID INTEGER PRIMARY KEY,
            CollegeID INTEGER,
            DegreeSpecID INTEGER,
            GPA REAL CHECK(GPA BETWEEN 0 AND 4),
            GraduationYear INTEGER,
            FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
            FOREIGN KEY (CollegeID) REFERENCES Colleges(CollegeID),
            FOREIGN KEY (DegreeSpecID) REFERENCES DegreeSpecializations(DegreeSpecID)
        );""",
        
        # 技能与特质表
        """CREATE TABLE IF NOT EXISTS Skills (
            StudentID INTEGER,
            SkillType TEXT CHECK(SkillType IN ('English', 'Logical', 'Quant', 'Domain')),
            Score REAL,
            PRIMARY KEY (StudentID, SkillType),
            FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
        );""",
        
        """CREATE TABLE IF NOT EXISTS PersonalityTraits (
            StudentID INTEGER PRIMARY KEY,
            Conscientiousness REAL,
            Agreeableness REAL,
            Extraversion REAL,
            Neuroticism REAL,
            Openness REAL,
            FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
        );""",
        
        # 薪资表
        """CREATE TABLE IF NOT EXISTS Salaries (
            StudentID INTEGER PRIMARY KEY,
            Salary REAL CHECK(Salary >= 0),
            FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
        );"""
    ]

    try:
        c = conn.cursor()
        for statement in sql_statements:
            c.execute(statement)
        print("所有数据表创建成功")
    except Error as e:
        print(e)

def create_indexes(conn):
    """ 创建索引 """
    indexes = [
        "CREATE INDEX IF NOT EXISTS idx_college_tier ON Colleges(Tier);",
        "CREATE INDEX IF NOT EXISTS idx_graduation_year ON CollegePerformance(GraduationYear);",
        "CREATE INDEX IF NOT EXISTS idx_salary_range ON Salaries(Salary);",
        "CREATE INDEX IF NOT EXISTS idx_city_state ON Cities(State);"
    ]
    
    try:
        c = conn.cursor()
        for index in indexes:
            c.execute(index)
        print("索引创建成功")
    except Error as e:
        print(e)

def create_views(conn):
    """ 创建视图 """
    view_sql = """
    CREATE VIEW IF NOT EXISTS StudentCompleteProfile AS
    SELECT 
        s.StudentID,
        s.Gender,
        s.DOB,
        ed.HighSchoolGrade,
        cp.GPA,
        sa.Salary,
        c.CollegeName,
        ds.Degree || ' - ' || ds.Specialization AS Major,
        (sk.Score - (SELECT MIN(Score) FROM Skills WHERE SkillType = sk.SkillType)) / 
        ((SELECT MAX(Score) FROM Skills WHERE SkillType = sk.SkillType) - 
         (SELECT MIN(Score) FROM Skills WHERE SkillType = sk.SkillType)) AS NormalizedSkill
    FROM Students s
    LEFT JOIN (
        SELECT StudentID, MAX(Percentage) AS HighSchoolGrade 
        FROM Education 
        WHERE EducationLevel = '12th'
        GROUP BY StudentID
    ) ed ON s.StudentID = ed.StudentID
    LEFT JOIN CollegePerformance cp ON s.StudentID = cp.StudentID
    LEFT JOIN Salaries sa ON s.StudentID = sa.StudentID
    LEFT JOIN Colleges c ON cp.CollegeID = c.CollegeID
    LEFT JOIN DegreeSpecializations ds ON cp.DegreeSpecID = ds.DegreeSpecID
    LEFT JOIN Skills sk ON s.StudentID = sk.StudentID;
    """
    
    try:
        c = conn.cursor()
        c.execute(view_sql)
        print("视图创建成功")
    except Error as e:
        print(e)

def main():
    database = "student_analysis.db"
    
    # 创建数据库连接
    conn = create_connection(database)
    
    if conn is not None:
        # 创建表
        create_tables(conn)
        
        # 创建索引
        create_indexes(conn)
        
        # 创建视图
        create_views(conn)
        
        # 提交并关闭连接
        conn.commit()
        conn.close()
        print("数据库初始化完成")
    else:
        print("无法建立数据库连接")

if __name__ == '__main__':
    main()